﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Adex;
using MediaMonitor.Common;
using MediaMonitor.Controls;
using Telerik.Web.UI;
using System.Web.Services;
using System.Collections;
using OfficeOpenXml;

namespace MediaMonitor.Users
{
    public partial class HistoricalData : Adex.BasePage
    {
        DateTime sDate = DateTime.MinValue, eDate = DateTime.MinValue;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["CompanyCode"] != null)
            {
                if (!IsPostBack)
                {
                    Utility.AddDateControlToPage(this.Page);
                    SetCalendarDates();
                }
            }
            else 
            {
                Response.Redirect("~/logout.aspx");
            }
        }
        private void SetCalendarDates()
        {
            DataTable dtHD = TblHistoricalDataForNewCommercial.Query().DISTINCT().SetSelectList("LaunchDay").ExecuteDataSet().Tables[0];
            foreach (DataRow dr in dtHD.Rows)
            {
                RadCalendarDay d1 = new RadCalendarDay(calS);
                d1.Date = Utility.ToDateTime(dr["LaunchDay"].ToString());
                d1.IsDisabled = false;
                d1.IsSelectable = true;
                d1.ItemStyle.BackColor = System.Drawing.Color.PowderBlue;
                d1.ItemStyle.ForeColor = System.Drawing.Color.White;
                calS.SpecialDays.Add(d1);

                RadCalendarDay d2 = new RadCalendarDay(calE);
                d2.Date = Utility.ToDateTime(dr["LaunchDay"].ToString());
                d2.IsDisabled = false;
                d2.IsSelectable = true;
                d2.ItemStyle.BackColor = System.Drawing.Color.PowderBlue;
                d2.ItemStyle.ForeColor = System.Drawing.Color.White;
                calE.SpecialDays.Add(d2);
            }
        }
        protected void btnExportData_Click(object sender, EventArgs e)
        {
            bool tmp1 = DateTime.TryParse(calS.SelectedDate.ToShortDateString(), out sDate);
            bool tmp2 = DateTime.TryParse(calE.SelectedDate.ToShortDateString(), out eDate);
            if (sDate < Utility.ToDateTime("01/01/2000"))
                sDate = Utility.ToDateTime("01/01/2000");
            if (eDate < Utility.ToDateTime("01/01/2000"))
                eDate = Utility.ToDateTime("01/01/2000");
            if ((sDate.ToString() != "1/1/2000 12:00:00 AM") && (eDate.ToString() != "1/1/2000 12:00:00 AM"))
            {
                DataTable dt = SPs.GetReportHistoricalNewCommercialData(sDate, eDate, Convert.ToInt32(Session["UserID"])).GetDataSet().Tables[0];
                if (dt.Rows.Count > 0)
                {
                    // Column name replacement
                    dt.Columns["LaunchDay"].ColumnName = "Departure Date";
                    dt.Columns["SubSector"].ColumnName = "Sub Sector";
                    dt.Columns["EstimatedHours1"].ColumnName = "Estimated hours for First Appearance";
                    dt.Columns["EstimatedHours2"].ColumnName = "Estimated hours for Second Appearance";
                    dt.Columns["EstimatedHours3"].ColumnName = "Estimated hours for Third Appearance";
                    dt.Columns["ProgramName1"].ColumnName = "Program Name for First Appearance";
                    dt.Columns["ProgramName2"].ColumnName = "Program Name for Second Appearance";
                    dt.Columns["ProgramName3"].ColumnName = "Program Name for Third Appearance";

                    // Removing extra column from the DataTable
                    DataColumnCollection columns = dt.Columns;
                    if (columns.Contains("ID"))
                    {
                        dt.Columns.Remove("ID");
                        dt.Columns.Remove("UserID");
                        dt.Columns.Remove("MediaType1");
                        dt.Columns.Remove("MediaType2");
                        dt.Columns.Remove("MediaType3");
                        dt.Columns.Remove("Upload commercial");
                        dt.Columns.Remove("CompanyCode");
                    }

                    // Writing excel file or Making a report on excel sheet
                    String strAutofilter = String.Empty;
                    strAutofilter = ReturnCharacterByPassingInterger(dt.Rows.Count);
                    using (ExcelPackage pck = new ExcelPackage())
                    {
                        try
                        {
                            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("HistoricalData");
                            ws.Cells["A2"].Value = "Comerciales Pedidos Por Los Clientes";
                            ws.Cells[2, 1, 2, 3].Merge = true;
                            ws.Cells[2, 1, 2, 3].Style.Font.Size = 20;
                            ws.Cells[2, 1, 2, 3].Style.Font.Bold = true;
                            ws.Cells["A5"].LoadFromDataTable(dt, true);
                            ws.DefaultColWidth = 20;
                            ws.Cells[5, 1, 5, 21].Style.Font.Bold = true;
                            ws.Cells[5, 1, 5, 21].Style.Font.Size = 12;
                        }
                        catch { }
                        //Download excel file
                        Response.Clear();
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.AddHeader("content-disposition", "attachment;  filename=HistoricalData.xlsx");
                        Response.BinaryWrite(pck.GetAsByteArray());
                        Response.End();
                    }
                }
                else
                {
                    string script = "alert(\"No Record found!\");";
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "ServerControlScript", script, true);
                }
            }
            else
            {
                string script = "alert(\"Please select date first!\");";
                ScriptManager.RegisterStartupScript(this, this.GetType(), "ServerControlScript", script, true);
            }
        }
        private String ReturnCharacterByPassingInterger(Int32 tblColumnCount)
        {
            String strAutofilter = String.Empty;
            switch (tblColumnCount)
            {
                case 1:
                    {
                        strAutofilter = "A"; break;
                    }
                case 2:
                    {
                        strAutofilter = "B"; break;
                    }
                case 3:
                    {
                        strAutofilter = "C"; break;
                    }
                case 4:
                    {
                        strAutofilter = "D"; break;
                    }
                case 5:
                    {
                        strAutofilter = "E"; break;
                    }
                case 6:
                    {
                        strAutofilter = "F"; break;
                    }
                case 7:
                    {
                        strAutofilter = "G"; break;
                    }
                case 8:
                    {
                        strAutofilter = "H"; break;
                    }
                case 9:
                    {
                        strAutofilter = "I"; break;
                    }
                case 10:
                    {
                        strAutofilter = "J"; break;
                    }
                case 11:
                    {
                        strAutofilter = "K"; break;
                    }
                case 12:
                    {
                        strAutofilter = "L"; break;
                    }
                case 13:
                    {
                        strAutofilter = "M"; break;
                    }
                case 14:
                    {
                        strAutofilter = "N"; break;
                    }
                case 15:
                    {
                        strAutofilter = "O"; break;
                    }
                case 16:
                    {
                        strAutofilter = "P"; break;
                    }
                case 17:
                    {
                        strAutofilter = "Q"; break;
                    }
                case 18:
                    {
                        strAutofilter = "R"; break;
                    }
                case 19:
                    {
                        strAutofilter = "S"; break;
                    }
                case 20:
                    {
                        strAutofilter = "T"; break;
                    }
                case 21:
                    {
                        strAutofilter = "U"; break;
                    }
                case 22:
                    {
                        strAutofilter = "V"; break;
                    }
                case 23:
                    {
                        strAutofilter = "W"; break;
                    }
                case 24:
                    {
                        strAutofilter = "X"; break;
                    }
                case 25:
                    {
                        strAutofilter = "Y"; break;
                    }
                case 26:
                    {
                        strAutofilter = "Z"; break;
                    }
            }
            return strAutofilter;
        }
    }
}